SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[aspnet_Pages](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[PageId] [uniqueidentifier] NOT NULL,
	[PageName] [nvarchar](256) NOT NULL,
	[LoweredPageName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_aspnet_Pages] PRIMARY KEY CLUSTERED 
(
	[PageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[aspnet_Pages]  WITH CHECK ADD  CONSTRAINT [FK_aspnet_Pages_aspnet_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO

ALTER TABLE [dbo].[aspnet_Pages] CHECK CONSTRAINT [FK_aspnet_Pages_aspnet_Applications]
GO



CREATE TABLE [dbo].[aspnet_PagesInRoles](
	[PageId] [uniqueidentifier] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_PagesInRoles] PRIMARY KEY CLUSTERED 
(
	[PageId] ASC,
	[RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[aspnet_PagesInRoles]  WITH CHECK ADD  CONSTRAINT [FK_aspnet_PagesInRoles_aspnet_Pages] FOREIGN KEY([PageId])
REFERENCES [dbo].[aspnet_Pages] ([PageId])
GO

ALTER TABLE [dbo].[aspnet_PagesInRoles] CHECK CONSTRAINT [FK_aspnet_PagesInRoles_aspnet_Pages]
GO

ALTER TABLE [dbo].[aspnet_PagesInRoles]  WITH CHECK ADD  CONSTRAINT [FK_aspnet_PagesInRoles_aspnet_Roles] FOREIGN KEY([RoleId])
REFERENCES [dbo].[aspnet_Roles] ([RoleId])
GO

ALTER TABLE [dbo].[aspnet_PagesInRoles] CHECK CONSTRAINT [FK_aspnet_PagesInRoles_aspnet_Roles]
GO


-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 11/11/2015
-- Description:	Create a Page...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Pages_CreatePage] 
    @ApplicationName  nvarchar(256),
    @PageName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF (EXISTS(SELECT PageId FROM dbo.aspnet_Pages WHERE LoweredPageName = LOWER(@PageName) AND ApplicationId = @ApplicationId))
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    INSERT INTO dbo.aspnet_Pages
                (ApplicationId, PageId, PageName, LoweredPageName)
         VALUES (@ApplicationId, NEWID(), @PageName, LOWER(@PageName))

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN(0)

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END
go

-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 11/11/2015
-- Description:	Delete a Page...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Pages_DeletePage] 
    @ApplicationName            nvarchar(256),
    @PageName                   nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    DECLARE @PageId   uniqueidentifier
    SELECT  @PageId = NULL
    SELECT  @PageId = PageId FROM dbo.aspnet_Pages WHERE LoweredPageName = LOWER(@PageName) AND ApplicationId = @ApplicationId

    IF (@PageId IS NULL)
    BEGIN
        SELECT @ErrorCode = 1
        GOTO Cleanup
    END

    DELETE FROM dbo.aspnet_PagesInRoles  WHERE @PageId = RoleId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    DELETE FROM dbo.aspnet_Pages WHERE @PageId = PageId  AND ApplicationId = @ApplicationId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN(0)

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END
go

-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 11/11/2015
-- Description:	Get all the pages...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Pages_GetAllPages]
 @ApplicationName           nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN
    SELECT PageName
    FROM   dbo.aspnet_Pages WHERE ApplicationId = @ApplicationId
    ORDER BY PageName
END
go

-- =============================================
-- Author:		Giuseppe Dattilo	
-- Create date: 21/05/2016
-- Description:	Get the Id of a page for applicationName 
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Pages_GetPageId]
	 @ApplicationName  nvarchar(256), 
	 @PageName  nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    DECLARE @pageId uniqueidentifier

    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

	SELECT @pageId = PageId FROM dbo.aspnet_Pages WHERE ApplicationId = @applicationID AND LOWER(@pageName) = LoweredPageName

	RETURN(0)
END
go

-- =============================================
-- Author:		[dbo].[aspnet_Pages_PageExists]
-- Create date: 11/11/2015
-- Description:	Checks if page exists...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Pages_PageExists]
    @ApplicationName  nvarchar(256),
    @PageName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(0)
    IF (EXISTS (SELECT PageName FROM dbo.aspnet_Pages WHERE LOWER(@PageName) = LoweredPageName AND ApplicationId = @ApplicationId ))
        RETURN(1)
    ELSE
        RETURN(0)
END
GO


-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 11/11/2015
-- Description:	Add Page(s) to Role(s)...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_PagesInRoles_AddPagesToRoles] 
	@ApplicationName  nvarchar(256),
	@PageNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000)
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)
	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbPages	table(PageId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num		int
	DECLARE @Pos		int
	DECLARE @NextPos	int
	DECLARE @Name		nvarchar(256)

	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		SELECT TOP 1 Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END

	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1

	WHILE(@Pos <= LEN(@PageNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @PageNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@PageNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@PageNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbPages
	  SELECT PageId
	  FROM   dbo.aspnet_Pages ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredPageName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		DELETE FROM @tbNames
		WHERE LOWER(Name) IN (SELECT LoweredPageName FROM dbo.aspnet_Pages au,  @tbPages u WHERE au.PageId = u.PageId)

		INSERT dbo.aspnet_Pages (ApplicationId, PageId, PageName, LoweredPageName)
		  SELECT @AppId, NEWID(), Name, LOWER(Name)
		  FROM   @tbNames

		INSERT INTO @tbPages
		  SELECT  PageId
		  FROM	dbo.aspnet_Pages au, @tbNames t
		  WHERE   LOWER(t.Name) = au.LoweredPageName AND au.ApplicationId = @AppId
	END

	IF (EXISTS (SELECT * FROM dbo.aspnet_PagesInRoles ur, @tbPages tu, @tbRoles tr WHERE tu.PageId = ur.PageId AND tr.RoleId = ur.RoleId))
	BEGIN
		SELECT TOP 1 PageName, RoleName
		FROM		 dbo.aspnet_PagesInRoles ur, @tbPages tu, @tbRoles tr, aspnet_Pages u, aspnet_Roles r
		WHERE		u.PageId = tu.PageId AND r.RoleId = tr.RoleId AND tu.PageId = ur.PageId AND tr.RoleId = ur.RoleId

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	INSERT INTO dbo.aspnet_PagesInRoles (PageId, RoleId)
	SELECT PageId, RoleId
	FROM @tbPages, @tbRoles

	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END
go

-- =============================================
-- Author:		[dbo].[aspnet_PagesInRoles_FindPagesInRole]
-- Create date: 11/11/2015
-- Description:	Find pages in role...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_PagesInRoles_FindPagesInRole]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256),
    @PageNameToMatch  nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
     DECLARE @RoleId uniqueidentifier
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.aspnet_Roles
     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.PageName
    FROM   dbo.aspnet_Pages u, dbo.aspnet_PagesInRoles ur
    WHERE  u.PageId = ur.PageId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredPageName LIKE LOWER(@PageNameToMatch)
    ORDER BY u.PageName
    RETURN(0)
END
go

-- =============================================
-- Author:		Giuseppe Dattilo	
-- Create date: 11/11/2015
-- Description:	GEt all pages in specific roles...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_PagesInRoles_GetPagesInRole] 
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
     DECLARE @RoleId uniqueidentifier
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.aspnet_Roles
     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.PageName
    FROM   dbo.aspnet_Pages u, dbo.aspnet_PagesInRoles ur
    WHERE  u.PageId = ur.PageId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
    ORDER BY u.PageName
    RETURN(0)
END
go

-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 20/05/2016
-- Description:	Get All the Pages in all the Roles. Create matrix table to manage the assignment of pages to roles
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_PagesInRoles_GetPagesInRoles]
   @ApplicationName  nvarchar(256)
AS
BEGIN

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    DECLARE @ApplicationId uniqueidentifier
    
	SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

	CREATE TABLE #PageRoles	(PageName NVARCHAR(256),RoleName nvarchar(256),Assigned int)

	--Populate Matrix Table for Pages and Roles...
	INSERT INTO #PageRoles 
		select P.PageName, R.RoleName, 1
		from dbo.aspnet_Roles R
		left join dbo.aspnet_PagesInRoles PR on R.RoleId = PR.RoleId
		left join [dbo].[aspnet_Pages] P on  P.PageId = PR.PageId
		where P.ApplicationId = @ApplicationId and R.ApplicationId = @ApplicationId

	-- Get distinct values of the PIVOT Column 
	SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
		   + QUOTENAME(RoleName)
	FROM (SELECT DISTINCT RoleName FROM #PageRoles) AS Roles
 
	-- Prepare the PIVOT query using the dynamic 
	SET @DynamicPivotQuery = 
	  N'SELECT PageName, ' + @ColumnName + '
		FROM #PageRoles
		PIVOT(Sum(Assigned) 
			  FOR RoleName IN (' + @ColumnName + ')) AS PVTTable
			  WHERE PageName IS NOT NULL'

	--Execute the Dynamic Pivot Query
	EXEC sp_executesql @DynamicPivotQuery
	drop table #PageRoles
	RETURN(0)

END
go

-- =============================================
-- Author:		Giuseppe Dattilo	
-- Create date: 11/11/2015
-- Description:	Get Roles for specific Page...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_PagesInRoles_GetRolesForPage] 
    @ApplicationName  nvarchar(256),
    @PageName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
    DECLARE @PageId uniqueidentifier
    SELECT  @PageId = NULL

    SELECT  @PageId = PageId
    FROM    dbo.aspnet_Pages
    WHERE   LoweredPageName = LOWER(@PageName) AND ApplicationId = @ApplicationId

    IF (@PageId IS NULL)
        RETURN(1)

    SELECT r.RoleName
    FROM   dbo.aspnet_Roles r, dbo.aspnet_PagesInRoles ur
    WHERE  r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.PageId = @PageId
    ORDER BY r.RoleName
    RETURN (0)
END
go

-- =============================================
-- Author:		Giuseppe Dattilo	
-- Create date: 11/11/2015
-- Description:	Checks if a page is configured for specific role...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_PagesInRoles_IsPageInRole] 
    @ApplicationName  nvarchar(256),
    @PageName         nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(2)
    DECLARE @PageId uniqueidentifier
    SELECT  @PageId = NULL
    DECLARE @RoleId uniqueidentifier
    SELECT  @RoleId = NULL

    SELECT  @PageId = PageId
    FROM    dbo.aspnet_Pages
    WHERE   LoweredPageName = LOWER(@PageName) AND ApplicationId = @ApplicationId

    IF (@PageId IS NULL)
        RETURN(2)

    SELECT  @RoleId = RoleId
    FROM    dbo.aspnet_Roles
    WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
        RETURN(3)

    IF (EXISTS( SELECT * FROM dbo.aspnet_PagesInRoles WHERE  PageId = @PageId AND RoleId = @RoleId))
        RETURN(1)
    ELSE
        RETURN(0)
END
go

-- =============================================
-- Author:		Giuseppe Dattilo
-- Create date: 11/11/2015
-- Description:	Remove pages from specific roles...
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_PagesInRoles_RemovePagesFromRoles]
	@ApplicationName  nvarchar(256),
	@PageNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000)
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)


	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames  table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles  table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbPages  table(PageId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num	  int
	DECLARE @Pos	  int
	DECLARE @NextPos  int
	DECLARE @Name	  nvarchar(256)
	DECLARE @CountAll int
	DECLARE @CountU	  int
	DECLARE @CountR	  int


	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
	SELECT @CountR = @@ROWCOUNT

	IF (@CountR <> @Num)
	BEGIN
		SELECT TOP 1 N'', Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END


	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1


	WHILE(@Pos <= LEN(@PageNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @PageNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@PageNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@PageNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbPages
	  SELECT PageId
	  FROM   dbo.aspnet_Pages ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredPageName AND ar.ApplicationId = @AppId

	SELECT @CountU = @@ROWCOUNT
	IF (@CountU <> @Num)
	BEGIN
		SELECT TOP 1 Name, N''
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT au.LoweredPageName FROM dbo.aspnet_Pages au,  @tbPages u WHERE u.PageId = au.PageId)

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(1)
	END

	SELECT  @CountAll = COUNT(*)
	FROM	dbo.aspnet_PagesInRoles ur, @tbPages u, @tbRoles r
	WHERE   ur.PageId = u.PageId AND ur.RoleId = r.RoleId

	IF (@CountAll <> @CountU * @CountR)
	BEGIN
		SELECT TOP 1 PageName, RoleName
		FROM		 @tbPages tu, @tbRoles tr, dbo.aspnet_Pages u, dbo.aspnet_Roles r
		WHERE		 u.PageId = tu.PageId AND r.RoleId = tr.RoleId AND
					 tu.PageId NOT IN (SELECT ur.PageId FROM dbo.aspnet_PagesInRoles ur WHERE ur.RoleId = tr.RoleId) AND
					 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_PagesInRoles ur WHERE ur.PageId = tu.PageId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	DELETE FROM dbo.aspnet_PagesInRoles
	WHERE PageId IN (SELECT PageId FROM @tbPages)
	  AND RoleId IN (SELECT RoleId FROM @tbRoles)
	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END
go

-- =============================================
-- Author:		Giuseppe Dattilo	
-- Create date: 21/05/2016
-- Description:	Get the Id of a role for applicationName 
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Roles_GetRoleId]
	 @ApplicationName  nvarchar(256), 
	 @RoleName  nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    DECLARE @roleId uniqueidentifier

    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

	SELECT @roleId = RoleId FROM dbo.aspnet_Roles WHERE ApplicationId = @applicationID AND LOWER(@roleName) = LoweredRoleName

	RETURN(0)
END